ALTER VIEW [dbo].[vw_Assembly_Wip_View]
AS
SELECT     LT.ProcessStep_ID, L.L_Vendor_Name, LT.Lot_Number, LT.Fab_Lot_Number, ISNULL(LT.Lot_Category, 'PROD') AS LOT_CATEGORY, LT.Wafer_Qty_In, 
                      LT.Qty_In, LT.Date_In, LT.Stage_Name, LT.Stage_Position, LT.Lot_Current_Status, AL.Expected_Date_Out, AL.Thickness, AL.Grind, P.Part_Number,
                          (SELECT     Part_Number
                            FROM          dbo.Product
                            WHERE      (Product_ID = CASE WHEN (P.P_PARENT_PRODUCT_ID IS NULL OR
                                                   P.P_PARENT_PRODUCT_ID = - 1) THEN P.PRODUCT_ID WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL 
                                                   THEN P.P_PARENT_PRODUCT_ID END)) AS BASE_PRODUCT, (CASE WHEN (P.P_PARENT_PRODUCT_ID IS NULL OR
                      P.P_PARENT_PRODUCT_ID = - 1) THEN P.PRODUCT_ID WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID END) 
                      AS BASE_PRODUCT_ID, PS.PS_Type_Name, PS.PS_Type, 5 AS PS_TYPE_ID, LT.Lot_ID, LT.Location_ID, NULL AS PACKAGE_TYPE
FROM         dbo.Lot_Table AS LT INNER JOIN
                      dbo.Assembly_Lot AS AL ON LT.Lot_ID = AL.Lot_Id INNER JOIN
                      dbo.Product AS P ON LT.ProcessStep_ID = P.ProcessStep_ID INNER JOIN
                      dbo.Location AS L ON LT.Location_ID = L.Location_ID INNER JOIN
                      dbo.Process_Step AS PS ON P.ProcessStep_ID = PS.ProcessStep_ID
WHERE     (LT.Lot_Current_Status = 'Running' OR
                      LT.Lot_Current_Status = 'Hold') AND (P.Obsolete <> 0) AND (LT.Qty_In > 0) AND (PS.PS_Type_Name = 'ASSEMBLY')